/*
 * @(#)ConvertToMsSql.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.dcivision.framework.Utility;

public class ConvertToMsSql {

  public static final String REVISION = "$Revision: 1.15.32.1 $";


  private static final Log log = LogFactory.getLog(ConvertToMsSql.class);
  private ArrayList inlist = new ArrayList();

  /**
   * replaceString
   *
   * Returns string after replacing the original string to the target string, no matter
   * the ori string wrapped by space or charater.
   *
   * @param   src Source string which going to be replaced
   * @param   ori Original string in the source string
   * @param   tar Target string which replaces the original string
   * @return      String after replacement taken place.
   */
  public static String replaceString(String src, String ori, String tar) {
    String result = null;

    if (src!=null && ori!=null && tar!=null){
      try {
        StringBuffer source = new StringBuffer(src);
        int appearedIndex = 0;
        int oriLength = ori.length();
        int tarLength = tar.length();
        int byPassingIndex = 0;
        // If you got exception here, please check your JDK version. JDK 1.4 is needed for this method.
        while (source.indexOf(ori,byPassingIndex)!=-1) {
          appearedIndex = source.indexOf(ori,byPassingIndex);
          if (ori.equals(source.substring(appearedIndex,appearedIndex+oriLength))) {
            source = source.replace(appearedIndex,appearedIndex+oriLength,tar);
            byPassingIndex = appearedIndex+tarLength;
          } else {
            byPassingIndex = appearedIndex;
          }
        }
        result = source.toString();
      } catch (Exception e) {
        log.error(e, e);
      }
    }
    return result;
  }

  /**
   * removeAndInsert
   *
   * Removes part of the original string starting at removeAndInsertStart and ending at removeEnd.
   * Then insert another string at the same position where the part was removed (ie. at removeAndInsertStart).
   *
   * @param str                   The original string
   * @param removeAndInsertStart  The index within the original string at which removal is to start
   * @param removeEnd             The index within the original string at which removal is to end (exclusive -
   *                              ie. does not remove the character at that index)
   * @param insertStr             The string to be inserted
   * @return                      The string after conversion
   */
  public static String removeAndInsert(
  String str, int removeAndInsertStart, int removeEnd, String insertStr) {

    String partBefore = str.substring(0, removeAndInsertStart);
    String partAfter = str.substring(removeEnd);

    str = partBefore + insertStr + partAfter;

    return str;
  }

  /**
   * removeAndInsert
   *
   * Removes part of the original string starting at iStart and ending at iEnd.
   * Then insert another string at the same position where the part was removed.
   *
   * @param str        The original string
   * @param oristart   The starting character of the original string in the source string
   * @param oriend     The ending character of the original string in the source string
   * @param tar        The string to be inserted
   * @return           The string after conversion
   */
  public final static String replaceBy(
  String src, String oristart, String oriend, String tar) {
    if (src.indexOf(oristart)<0) {
      return(src);
    }

    String sResult =null;
    int iStart=0;
    int iEnd=0;
    if (src!=null && oristart!=null && tar!=null){
      iStart = src.indexOf(oristart);
      if (iStart>0){
         iEnd = src.lastIndexOf(oriend);
         sResult = removeAndInsert(src, iStart, iEnd+1, tar);
      }
    }
    return sResult;

  }

  public final static String replaceList(String src,String oristart,String oriend,String tar){
    String sResult=null;
    int iStart=0;
    int iEnd=0;
    if(src.indexOf(oristart)<0){
      return (src);
    }

    if (src!=null && oristart!=null && tar!=null){
      iStart=src.indexOf(oristart);
      if(iStart>0){
        iEnd = src.lastIndexOf(oriend);
        sResult = removeAndInsert(src,iStart,iEnd+1,tar);
      }
    }
    return sResult;
  }

  /**
   * ConvertToMsSql
   *
   * To read the whole source file line by line
   * and store it in the inlist.
   *
   * @param   fn Source filename whose text are going to be replaced
   */
  public ConvertToMsSql(String fn) throws IOException {
    FileReader fr = new FileReader(fn);
    BufferedReader br = new BufferedReader(fr);
    String sLn;
    while ((sLn = br.readLine()) != null)
        inlist.add(sLn);
    br.close();
  }

  /**
   * getLine
   *
   * To get a line from the inlist
   *
   * @param   n the number of line (nth) to be got
   * @return    The indicated line of Sting
   */
  public String getLine(int n) {
    if (n < 0)
      throw new IllegalArgumentException();

    return (n < inlist.size() ? (String)inlist.get(n) : null);
  }

  public static void main(String args[]) {

    int i = 0;
    String sLine;
    String fLine;
    String fieldName="";
    String tableName="";
    ArrayList lineList=new ArrayList();
    ArrayList tableList=new ArrayList();

    if (args.length < 2) {
      System.err.println("USAGE: java ConvertToMsSQL <in file> <out file>");
      System.exit(1);
    }

    try {
      File outputFile = new File(args[1]);
      FileWriter fo = new FileWriter(outputFile);

      ConvertToMsSql LineCache = new ConvertToMsSql(args[0]);

      while ((sLine = LineCache.getLine(i++)) != null) {
        if (sLine.indexOf("MySQL dump") < 0 && sLine.indexOf("-- Server version") < 0 && sLine.indexOf("-- Host:") < 0 && sLine.indexOf("-- -------") < 0  && !"--".equals(sLine)) {
          sLine = replaceString(sLine, "varchar", "nvarchar");
          sLine = replaceString(sLine, "longtext", "text");
          sLine = replaceString(sLine, ";", "\n" + "GO" + "\n");
          sLine = replaceString(sLine, "NOT NULL default ''", "NOT NULL");
          sLine = replaceString(sLine, "default NULL", "NULL");
          sLine = replaceString(sLine, "default '0'", "");
          sLine = replaceString(sLine, "default '0000-00-00 00:00:00'", "");
          sLine = replaceString(sLine, "default ''", "");
          sLine = replaceString(sLine, " TYPE=MyISAM", "");
          sLine = replaceString(sLine, " TYPE=InnoDB", "");
          sLine = replaceString(sLine, "text", "nvarchar(500)");
          //if exist # and start with #
          if (getSLineOfC(sLine, "#")) {
            sLine = replaceString(sLine, "# ", "---");
          }
          sLine = replaceString(sLine, "''", "");
          sLine = replaceString(sLine, "blob", "IMAGE");
          sLine = replaceString(sLine, "UNIQUE KEY", "UNIQUE");
          sLine = replaceString(sLine, "double", "float");
          sLine = replaceString(sLine, "PACK_KEYS=1", "");
          sLine = replaceString(sLine, "longblob", "IMAGE");
          sLine = replaceString(sLine, "longIMAGE", "IMAGE");
          sLine = replaceString(sLine, "'0000-00-00 00:00:00'", "GETDATE()");
          sLine = replaceBy(sLine, "bigint(", ")", "int");
          sLine = replaceBy(sLine, "smallint(", ")", "smallint");
          sLine = replaceBy(sLine, "UNIQUE", " (", "UNIQUE ");
          sLine = replaceBy(sLine, "int(", ")", "int");
          tableName = getTableName(sLine); //get table name
          if (!Utility.isEmpty(tableName)) {
            tableList.add(tableName); //set all table name into the tableList
          }
          fieldName = getFieldName(sLine); //get field name of KEY
          if (!Utility.isEmpty(fieldName)) {
            fLine = getIndexString(fieldName, tableList); //get create index script
            lineList.add(fLine); //set all the create index script into lineList.
            sLine = ""; //delete the line of KEY
          }

          if (sLine.indexOf("PRIMARY KEY") >= 0) {
            String tmpLine = LineCache.getLine(i);
            if (tmpLine.indexOf("UNIQUE") < 0) {
              if (sLine.trim().endsWith(",")) {
                sLine = sLine.substring(0, sLine.length() - 1);
              }
            }
          }

          fo.write(sLine);
          fo.write("\r\n");
        }
      }
      fo.write("GO"+"\r\n");
      for(int j=0;j<lineList.size();j++){
        fo.write((String)lineList.get(j) );
        fo.write("\r\n"+"GO"+"\r\n");
      }
      fo.write("\r\n");

      fo.close();
    } catch (IOException e) {
      System.err.println(e);
    }
  }

  /**
   * get the field name if contain KEY
   * @param sLine                           the sLine
   * @return fieldName                  field name
   */
  public final static String getFieldName(String sLine){
    int startC=0;
    int endC=0;
    String fieldName="";
    if(sLine!=null){
      sLine=sLine.trim() ;
      //if the line start with KEY,get the field name
      if(sLine.indexOf("KEY")==0){
        startC=sLine.indexOf("(");
        if(startC!=-1){
          endC=sLine.indexOf(")");
          if(endC!=-1){
            fieldName=sLine.substring(startC+1,endC);
          }
        }
      }
    }
    return fieldName;
  }

  /**
   * get create index script
   * @param fieldName                  the key field name
   * @param tableList                  all table list
   * @return sLine;                    the create index script
   */
  public final static String getIndexString(String fieldName,ArrayList tableList){
    String sLine="";
    if(!Utility.isEmpty(fieldName)){
      sLine="CREATE NONCLUSTERED INDEX "+fieldName+" ON "+tableList.get(tableList.size()-1)+" ("+fieldName+")";
    }
    return sLine;

  }

  /**
   * get all table name
   * @param sLine
   * @return tableName
   */
  public final static String getTableName(String sLine){
    String tableName="";
    int startC=0;
    int endC=0;
    if(!Utility.isEmpty(sLine)){
      sLine.trim();
      //if the line contain create table,get the table name
      if(sLine.indexOf("CREATE TABLE")==0){
          startC=sLine.indexOf("TABLE");
          endC=sLine.indexOf("(");
          tableName=sLine.substring(startC+"TABLE".length(),endC);
      }
    }
    return tableName;
  }

  /**
   * verdict the if the line contain a peculiarity character and start withe it or not.
   * @param sLine
   * @param a                        peculiarity character
   * @return true or false
   */
  public final static boolean getSLineOfC(String sLine,String a){
    if(sLine.indexOf(a)!=-1){
      sLine.trim();
      if(sLine.startsWith(a)){
           return true;
      }else{
        return false;
      }
    }else{
      return false;
    }
  }

}